﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace ClientRegister
{
    public class StudentDataProvider
    {

        GetNextID getId = new GetNextID();

        ConnectDB connect = new ConnectDB();
        SqlConnection cnn;
        SqlCommand cmd;
        SqlDataAdapter ada;

        //Lay thong tin sinh vien co ma so la id
        public string SelectStudentsAndConvertToXMLString(string id)
        {
            string query = string.Format("select * from Students where ID = '{0}' for xml auto,elements", id);
            string xmlstring = "";
            cnn = connect.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);

            xmlstring = cmd.ExecuteScalar().ToString();
            return xmlstring;
        }

        //Lay Id cua record vua insert vao table
        public DataSet SelectIDNewRecord()
        {
            string query = string.Format("Select ID from Students where NewRecord = 1");
            cnn = connect.Connect();
            DataSet ds = new DataSet();
            try
            {
                cnn.Open();
                cmd = new SqlCommand(query, cnn);
                ada = new SqlDataAdapter(cmd);
                ada.Fill(ds);
                return ds;
            }
            catch (Exception) { throw; }
            finally
            {
                if (cnn != null && cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }

        //Cap nhat lai cot NewRecord sau khi duoc dua len server
        public void UpdateNewRecord()
        {
            string query = string.Format("update Students set NewRecord = 0 where NewRecord = 1");
            cnn = connect.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            cmd.ExecuteNonQuery();
        }

        //Lay Id cua record vua insert vao table
        public DataSet SelectIDNewRecordUpdate()
        {
            string query = string.Format("Select ID from Students where NewRecord = 2");
            cnn = connect.Connect();
            DataSet ds = new DataSet();
            try
            {
                cnn.Open();
                cmd = new SqlCommand(query, cnn);
                ada = new SqlDataAdapter(cmd);
                ada.Fill(ds);
                return ds;
            }
            catch (Exception) { throw; }
            finally
            {
                if (cnn != null && cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }

        //Cap nhat lai cot NewRecord sau khi duoc dua len server
        public void UpdateNewRecordUpdate()
        {
            string query = string.Format("update Students set NewRecord = 0 where NewRecord = 2");
            cnn = connect.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            cmd.ExecuteNonQuery();
        }

        public int insertStudent(string ID,string name, int gender, string birthday, string job, int phone, string email, string address)
        {
            

            cnn = connect.Connect();
            cnn.Open();
            cmd = new SqlCommand("insert into Students(ID,FullName, GenderId, Birthday, Career, Email, Phone, Address,NewRecord, CreateDateTime) " +
                                "values(@ID, @name, @gender, @birhday, @job, @email, @phone, @address,@NewRecord, @CreateDateTime)", cnn);
            cmd.Parameters.AddWithValue("@ID", ID);
            cmd.Parameters.AddWithValue("@name", name);
            cmd.Parameters.AddWithValue("@gender", gender);
            cmd.Parameters.AddWithValue("@birhday", DateTime.Parse(birthday));
            cmd.Parameters.AddWithValue("@job", job);
            cmd.Parameters.AddWithValue("@email", email);
            cmd.Parameters.AddWithValue("@phone", phone);
            cmd.Parameters.AddWithValue("@address", address);
            cmd.Parameters.AddWithValue("@NewRecord", 1);
            cmd.Parameters.AddWithValue("@CreateDateTime", DateTime.Now.ToString());
            return cmd.ExecuteNonQuery();
        }
        public DataSet loadStudentInCourse(string idCourse)
        {
            string query = string.Format("Select Students.ID,FullName, Classes.NameClass,Students.GenderId,Birthday,Career,Email,Phone,Address,Students.Note,Students.CreateDateTime from Students,StudentInClass,Classes,ClassInCourse where Students.ID = StudentInClass.IDStudent and StudentInClass.IDClass = Classes.ID and Classes.ID = ClassInCourse.IDClass And ClassInCourse.IDCourse ='{0}'", idCourse);
            cnn = connect.Connect();
            DataSet ds = new DataSet();
            try
            {
                cnn.Open();
                cmd = new SqlCommand(query, cnn);
                ada = new SqlDataAdapter(cmd);
                ada.Fill(ds);
                return ds;
            }
            catch (Exception) { throw; }
            finally
            {
                if (cnn != null && cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }

        public DataSet loadStudentInClass(string idClass,string idCourse)
        {
            string query = string.Format("Select Students.ID,FullName,Classes.NameClass,Students.GenderId,Birthday,Career,Email,Phone,Address,Students.Note,Students.CreateDateTime from Students,StudentInClass,Classes,ClassInCourse where Students.ID = StudentInClass.IDStudent and StudentInClass.IDClass = Classes.ID and Classes.ID = ClassInCourse.IDClass and ClassInCourse.IDClass = '{0}' And ClassInCourse.IDCourse ='{1}'", idClass, idCourse);
            cnn = connect.Connect();
            DataSet ds = new DataSet();
            try
            {
                cnn.Open();
                cmd = new SqlCommand(query, cnn);
                ada = new SqlDataAdapter(cmd);
                ada.Fill(ds);
                return ds;
            }
            catch (Exception) { throw; }
            finally
            {
                if (cnn != null && cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }

        public DataSet loadInfo(string idStu)
        {
            string query = string.Format("Select * from Students where ID = '{0}'", idStu);
            cnn = connect.Connect();
            DataSet ds = new DataSet();
            try
            {
                cnn.Open();
                cmd = new SqlCommand(query, cnn);
                ada = new SqlDataAdapter(cmd);
                ada.Fill(ds);
                return ds;
            }
            catch (Exception) { throw; }
            finally
            {
                if (cnn != null && cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }

        public int UpdateStudent(string ID, string FullName, int GenderId, string Birthday, string Career,
                                 string Email, int Phone, string Address, string Note)
        {
            Service.RegisterService gettime = new Service.RegisterService();
            string currTime = gettime.currentTime();
            string query = string.Format("update Students set FullName ='{0}',"
                +" GenderId = {1}, Birthday = '{2}', Career = '{3}', Email = '{4}',"
                + " Phone = {5}, Address = '{6}', Note = '{8}' ,NewRecord = 2, LastUpdate = '{9}' where ID = '{7}'", FullName, GenderId, DateTime.Parse(Birthday), Career, Email, Phone, Address, ID, Note, DateTime.Parse(currTime));
            cnn = connect.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            return cmd.ExecuteNonQuery();
        }

        public DataSet loadOldInfoID(string StuId)
        {
            string query = string.Format("select DISTINCT Students.ID,FullName,Students.GenderId,Birthday,Career,Email,Phone,Address,Students.CreateDateTime " +
                "from Students, StudentInClass where Students.ID = '{0}' and Students.ID = StudentInClass.IDStudent", StuId);
            cnn = connect.Connect();
            DataSet ds = new DataSet();
            try
            {
                cnn.Open();
                cmd = new SqlCommand(query, cnn);
                ada = new SqlDataAdapter(cmd);
                ada.Fill(ds);
                return ds;
            }
            catch (Exception) { throw; }
            finally
            {
                if (cnn != null && cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }
        public DataSet loadOldInfoName(string StuName)
        {
            string query = string.Format("select Students.ID,FullName,Students.GenderId,Birthday,Career,Email,Phone,Address,Students.CreateDateTime " +
                "from Students, StudentInClass where Students.FullName = '{0}' and Students.ID = StudentInClass.IDStudent", StuName);
            cnn = connect.Connect();
            DataSet ds = new DataSet();
            try
            {
                cnn.Open();
                cmd = new SqlCommand(query, cnn);
                ada = new SqlDataAdapter(cmd);
                ada.Fill(ds);
                return ds;
            }
            catch (Exception) { throw; }
            finally
            {
                if (cnn != null && cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }
        public DataSet loadInforStuForDeleteID(string StuId)
        {
            string query = string.Format("select Students.ID,FullName,Students.GenderId,Birthday,Career,Email,Phone,Address,Note,Students.CreateDateTime " +
               "from Students where Students.ID = '{0}'", StuId);
            cnn = connect.Connect();
            DataSet ds = new DataSet();
            try
            {
                cnn.Open();
                cmd = new SqlCommand(query, cnn);
                ada = new SqlDataAdapter(cmd);
                ada.Fill(ds);
                return ds;
            }
            catch (Exception) { throw; }
            finally
            {
                if (cnn != null && cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }

        public int DeleteStudent(string ID)
        {
            string query = string.Format("delete from Students where ID = '{0}'", ID);
            cnn = connect.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            return cmd.ExecuteNonQuery();
        }
    }
}
